﻿using System;
using System.Collections;
using System.Collections.Generic;
using Microsoft.Office.Interop.Excel;

namespace ExcelTools
{
  // ==================================================================================
  /// <summary>
  /// This type represents an Excel worksheet.
  /// </summary>
  /// <remarks>
  /// This type is a wrapper class around the Worksheet class in the Excel PIA.
  /// </remarks>
  // ==================================================================================
  public sealed class ExcelWorksheet
  {
    #region Private fields

    private readonly Worksheet _Worksheet;
    private readonly ExcelWorkbook _Parent;

    #endregion

    public ExcelWorksheet(ExcelWorkbook workbook, Worksheet worksheet)
    {
      _Parent = workbook;
      _Worksheet = worksheet;
    }

    #region Public properties

    // --------------------------------------------------------------------------------
    /// <summary>
    /// Gets the Microsoft.Office.Tools.Excel.Worksheet object behind this instance.
    /// </summary>
    // --------------------------------------------------------------------------------
    public Worksheet Worksheet
    {
      get { return _Worksheet; }
    }

    // --------------------------------------------------------------------------------
    /// <summary>
    /// Gets or sets the name of the worksheet.
    /// </summary>
    // --------------------------------------------------------------------------------
    public string Name
    {
      get { return _Worksheet.Name; }
      set { _Worksheet.Name = value; }
    }

    // --------------------------------------------------------------------------------
    /// <summary>
    /// Gets the parent workbook of this worksheet.
    /// </summary>
    // --------------------------------------------------------------------------------
    public ExcelWorkbook Parent
    {
      get { return _Parent; }
    }

    #endregion

    #region Public methods

    // --------------------------------------------------------------------------------
    /// <summary>
    /// Activates the specified worksheet.
    /// </summary>
    // --------------------------------------------------------------------------------
    public void Activate()
    {
      _Worksheet.Activate();
    }

    // --------------------------------------------------------------------------------
    /// <summary>
    /// Copies this worksheet before the worksheet specified by index.
    /// </summary>
    /// <param name="index">Indexof worksheet to copy this sheet before.</param>
    /// <returns>The copy of the original worksheet.</returns>
    /// <remarks>
    /// The new copy becomes the active sheetn of the workbook.
    /// </remarks>
    // --------------------------------------------------------------------------------
    public ExcelWorksheet CopyBefore(int index)
    {
      object refSheet = _Parent.Workbook.Worksheets[index];
      Worksheet.Copy(refSheet, Type.Missing);
      return _Parent.ActiveSheet;
    }

    // --------------------------------------------------------------------------------
    /// <summary>
    /// Copies this worksheet before the specified worksheet.
    /// </summary>
    /// <param name="excelSheet">Sheet to copy this sheet before.</param>
    /// <returns>The copy of the original worksheet.</returns>
    /// <remarks>
    /// The new copy becomes the active sheetn of the workbook.
    /// </remarks>
    // --------------------------------------------------------------------------------
    public ExcelWorksheet CopyBefore(ExcelWorksheet excelSheet)
    {
      Worksheet.Copy(excelSheet.Worksheet, Type.Missing);
      return _Parent.ActiveSheet;
    }

    #endregion
  }


  // ==================================================================================
  /// <summary>
  /// This type represents a collection of Excel worksheets that can be indexed by 
  /// either an integer index or by the name of the sheet.
  /// </summary>
  /// <remarks>
  /// This collection is a wrapper class around the 
  /// Microsoft.Office.Interop.Excel.Sheets type.
  /// </remarks>
  // ==================================================================================
  public sealed class ExcelWorksheetCollection: IEnumerable<ExcelWorksheet>
  {
    #region Private fields

    private readonly Workbook _Workbook;
    private readonly ExcelWorkbook _Parent;

    #endregion

    #region Lifecycle methods

    // --------------------------------------------------------------------------------
    /// <summary>
    /// Creates a new instance of this worksheet collection based on the specified
    /// workbook instance.
    /// </summary>
    /// <param name="excelWorkbook">Workbook instance</param>
    // --------------------------------------------------------------------------------
    internal ExcelWorksheetCollection(ExcelWorkbook excelWorkbook)
    {
      _Parent = excelWorkbook;
      _Workbook = excelWorkbook.Workbook;
    }

    #endregion

    #region Public properties

    // --------------------------------------------------------------------------------
    /// <summary>
    /// Gets the worksheet specified by the given one-based index.
    /// </summary>
    /// <param name="index">One-based index of the worksheet.</param>
    /// <returns>
    /// The worksheet specified by index.
    /// </returns>
    // --------------------------------------------------------------------------------
    public ExcelWorksheet this[int index]
    {
      get
      {
        if (index < 1 || index > Count)
        {
          throw new IndexOutOfRangeException(
            string.Format("Worksheet index must be between 1 and {0}", Count));
        }
        return new ExcelWorksheet(Parent, _Workbook.Worksheets[index] as Worksheet);
      }
    }

    // --------------------------------------------------------------------------------
    /// <summary>
    /// Gets the count of worksheets within this collection.
    /// </summary>
    // --------------------------------------------------------------------------------
    public int Count
    {
      get { return _Workbook.Worksheets.Count; }
    }

    // --------------------------------------------------------------------------------
    /// <summary>
    /// Gets the parent workbook of this collection.
    /// </summary>
    // --------------------------------------------------------------------------------
    public ExcelWorkbook Parent
    {
      get { return _Parent; }
    }

    #endregion

    #region Add methods

    // --------------------------------------------------------------------------------
    /// <summary>
    /// Add a new worksheet after the last one.
    /// </summary>
    /// <returns>The newly added worksheet instance.</returns>
    // --------------------------------------------------------------------------------
    public ExcelWorksheet Add()
    {
      object newSheet = _Workbook.Worksheets.Add(Type.Missing, Type.Missing, 1, 
        XlSheetType.xlWorksheet);
      return new ExcelWorksheet(_Parent, newSheet as Worksheet);
    }

    // --------------------------------------------------------------------------------
    /// <summary>
    /// Adds a new worksheet before the one specified by the index.
    /// </summary>
    /// <param name="index">Worksheet index.</param>
    /// <returns>
    /// The new worksheet object added to the collection of worksheets.
    /// </returns>
    // --------------------------------------------------------------------------------
    public ExcelWorksheet AddBefore(int index)
    {
      object refSheet = _Workbook.Worksheets[index];
      object newSheet = _Workbook.Worksheets.Add(refSheet, Type.Missing, 1,
        XlSheetType.xlWorksheet);
      return new ExcelWorksheet(_Parent, newSheet as Worksheet);
    }

    // --------------------------------------------------------------------------------
    /// <summary>
    /// Adds a new worksheet before the one specified as the argument.
    /// </summary>
    /// <param name="excelSheet">Worksheet object.</param>
    /// <returns>
    /// The new worksheet object added to the collection of worksheets.
    /// </returns>
    // --------------------------------------------------------------------------------
    public ExcelWorksheet AddBefore(ExcelWorksheet excelSheet)
    {
      object newSheet = _Workbook.Worksheets.Add(excelSheet.Worksheet, Type.Missing, 1,
        XlSheetType.xlWorksheet);
      return new ExcelWorksheet(_Parent, newSheet as Worksheet);
    }

    // --------------------------------------------------------------------------------
    /// <summary>
    /// Adds a new worksheet after the one specified by the index.
    /// </summary>
    /// <param name="index">Worksheet index.</param>
    /// <returns>
    /// The new worksheet object added to the collection of worksheets.
    /// </returns>
    // --------------------------------------------------------------------------------
    public ExcelWorksheet AddAfter(int index)
    {
      object refSheet = _Workbook.Worksheets[index];
      object newSheet = _Workbook.Worksheets.Add(Type.Missing, refSheet, 1,
        XlSheetType.xlWorksheet);
      return new ExcelWorksheet(_Parent, newSheet as Worksheet);
    }

    // --------------------------------------------------------------------------------
    /// <summary>
    /// Adds a new worksheet after the one specified as the argument.
    /// </summary>
    /// <param name="excelSheet">Worksheet object.</param>
    /// <returns>
    /// The new worksheet object added to the collection of worksheets.
    /// </returns>
    // --------------------------------------------------------------------------------
    public ExcelWorksheet AddAfter(ExcelWorksheet excelSheet)
    {
      object newSheet = _Workbook.Worksheets.Add(Type.Missing, excelSheet.Worksheet, 1,
        XlSheetType.xlWorksheet);
      return new ExcelWorksheet(_Parent, newSheet as Worksheet);
    }

    #endregion

    #region IEnumerable and IEnumerable<ExcelWorksheet> implementations

    // --------------------------------------------------------------------------------
    /// <summary>
    /// Strongly-typed enumerator for the worksheets in the collection.
    /// </summary>
    // --------------------------------------------------------------------------------
    IEnumerator<ExcelWorksheet> IEnumerable<ExcelWorksheet>.GetEnumerator()
    {
      for (int i = 1; i <= Count; i++) yield return this[i];
    }

    // --------------------------------------------------------------------------------
    /// <summary>
    /// Object enumerator for the worksheets in the collection.
    /// </summary>
    // --------------------------------------------------------------------------------
    IEnumerator IEnumerable.GetEnumerator()
    {
      return ((IEnumerable<ExcelWorksheet>) this).GetEnumerator();
    }

    #endregion
  }
}